﻿#region Header
// ------------------------ Licence / Copyright ------------------------
// 
// ScrumTable for Scrum Meeting Support
// Copyright © HSR - Hochschule für Technik Rapperswil
// All Rights Reserved
// 
// Author:
//  Michael Gfeller Silvan Gehrig Patrick Boos
// 
// ---------------------------------------------------------------------
#endregion

#region Usings

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using ClosedXML.Excel;
using ScrumTable.Common.Collections;
using ScrumTable.Common.Data;
using ScrumTable.Common.Excel.OpenXmlSdk.Extensions;

#endregion

namespace ScrumTable.Common.Excel.OpenXmlSdk
{
    /// <summary>
    /// Provides the facilities for de-/serialization of an open xml sdk file.
    /// </summary>
    internal class WorkbookDeserializer
    {
        #region Declarations
        //--------------------------------------------------------------------
        // Declarations
        //--------------------------------------------------------------------

        private const string HiddenSheetIndicator = "_";
        private readonly string _idColumnName;

        private DataSet _resultWorkbook;
        private XLWorkbook _wbContext;

        #endregion

        #region Properties
        //--------------------------------------------------------------------
        // Properties
        //--------------------------------------------------------------------

        #endregion

        #region Constructors / Destructor
        //--------------------------------------------------------------------
        // Constructors / Destructor
        //--------------------------------------------------------------------

        internal WorkbookDeserializer(string idColumnName)
        {
            PreCondition.AssertNotNullOrEmpty(idColumnName, "idColumnName");

            _idColumnName = idColumnName;
        }

        #endregion

        #region Methods
        //--------------------------------------------------------------------
        // Methods
        //--------------------------------------------------------------------

        /// <summary>
        /// Loads the data of the underlying open xml file.
        /// </summary>
        internal DataSet LoadData(string workbookPath)
        {
            using (_wbContext = new XLWorkbook(workbookPath))
            {
                _resultWorkbook = new DataSet();
                _resultWorkbook.BeginInit();

                try
                {
                    LoadSheets();
                    LoadSheetData();   
                }
                finally
                {
                    _resultWorkbook.EndInit();
                    _resultWorkbook.AcceptChanges();                    
                }
            }
            return _resultWorkbook;
        }

        private void LoadSheets()
        {
            foreach (IXLWorksheet worksheet in _wbContext.Worksheets)
            {
                if (!string.IsNullOrEmpty(worksheet.Name)
                    && !(worksheet.Name.StartsWith(HiddenSheetIndicator) && worksheet.Name.EndsWith(HiddenSheetIndicator)))
                {
                    _resultWorkbook.EnsureDataTable(worksheet.Name);
                }
            }
        }

        private void LoadSheetData()
        {
            foreach (DataTable deserializedSheet in _resultWorkbook.GetTables())
            {
                IXLWorksheet worksheet;

                if (!_wbContext.Worksheets.TryGetWorksheet(deserializedSheet.TableName, out worksheet)) { continue; }
                bool isHeader = true;

                foreach (IXLRow row in worksheet.Rows())
                {
                    if (!row.AccommodatesValues()) { continue; }

                    if (isHeader)
                    {
                        if (HasHeaderRow(row))
                        {
                            LoadHeaderRowAsColumns(deserializedSheet, row);
                        }
                        else
                        {
                            LoadGenericColumns(deserializedSheet, row);
                            LoadSheetDataRow(deserializedSheet, row);
                        }
                        isHeader = false;
                    }
                    else
                    {
                        LoadSheetDataRow(deserializedSheet, row);
                    }
                }
            }
        }

        private void LoadSheetDataRow(DataTable sheet, IXLRow sheetRow)
        {
            DataRow dataRow = sheet.NewRow();
            IDictionary<int, IXLCell> cells =
                sheetRow.Cells().Convert(
                    cell =>
                        {
                            return new KeyValuePair<int, IXLCell>(
                                cell.Address.ColumnNumber - 1,
                                cell);
                        });
            
            for (int i = 0; i < dataRow.Table.Columns.Count; ++i)
            {
                if (cells.ContainsKey(i))
                {
                    // load value from excel sheet
                    dataRow[dataRow.Table.Columns[i]] = new DataValue(
                        cells[i].GetValueAvoidEmpty(),
                        (cells[i].Hyperlink != null) ? cells[i].Hyperlink.ExternalAddress : null,
                        CellReference.ToReference(cells[i].Address.ColumnLetter, cells[i].Address.RowNumber));
                }
                else
                {
                    // initialize value placeholder (for later value set)
                    dataRow[dataRow.Table.Columns[i]] = new DataValue(
                        null,
                        null,
                        CellReference.ToReference(
                            dataRow.Table.Columns[i].Caption,
                            Convert.ToInt32(sheetRow.RowNumber())));
                }
            }

            if (dataRow.IsNotContainedByPrimaryKey() && dataRow.AccommodatesValues())
            {
                dataRow.Table.Rows.Add(dataRow);
            }
        }

        private bool HasHeaderRow(IXLRow row)
        {
            return !(from cell in row.Cells()
                     where Equals(cell.Value, _idColumnName)
                     select cell).IsEmpty();
        }

        private void LoadGenericColumns(DataTable sheet, IXLRow row)
        {
            foreach (IXLCell column in row.Worksheet.CellsUsed())
            {
                DataColumn dataColumn = sheet.GetColumn(column.Address.ColumnNumber - 1);
                dataColumn.ColumnName = column.Address.ColumnLetter;
                dataColumn.Caption = dataColumn.ColumnName;
                dataColumn.SetCellRef(CellReference.ToReference(column.Address.ColumnLetter, column.Address.RowNumber));
            }
        }

        private void LoadHeaderRowAsColumns(DataTable sheet, IXLRow row)
        {
            IList<DataColumn> pkColumns = new List<DataColumn>();

            foreach (KeyValuePair<string, CellToColumnMapping> columnCellPars in IndexHeaderCellsAsColumnsByName(row))
            {
                DataColumn dataColumn = sheet.GetColumn(columnCellPars.Value.DataColumnIndex);
                dataColumn.ColumnName = columnCellPars.Key;
                dataColumn.Caption = columnCellPars.Value.ExcelCellRef.ColumnName;
                dataColumn.SetCellRef(columnCellPars.Value.ExcelCellRef);

                if (dataColumn.ColumnName == _idColumnName)
                {
                    pkColumns.Add(dataColumn);
                }
            }
            sheet.PrimaryKey = pkColumns.ToArray();
        }

        private IDictionary<string, CellToColumnMapping> IndexHeaderCellsAsColumnsByName(IXLRow row)
        {
            IDictionary<string, CellToColumnMapping> nameCellPairs = new Dictionary<string, CellToColumnMapping>();

            /*
             * pre process all cells:
             *   all header names must be unique.
             *   duplicated header names are not allowed so a counter will increase the the name.
             */
            foreach (var cell in row.Cells())
            {
                CellReference parsedRef = CellReference.ToReference(cell.Address.ColumnLetter, cell.Address.RowNumber);
                string columnNameRaw = (cell.GetValue<string>() ?? string.Empty);

                if (string.IsNullOrWhiteSpace(columnNameRaw))
                {
                    columnNameRaw = parsedRef.ColumnName;
                }

                string finalColumnName = columnNameRaw;

                // make sure the column name is unique
                for (int i = 1; nameCellPairs.ContainsKey(finalColumnName); ++i)
                {
                    finalColumnName += "_" + i;
                }
                nameCellPairs[finalColumnName] = new CellToColumnMapping { DataColumnIndex = cell.Address.ColumnNumber - 1, ExcelCell = cell, ExcelCellRef = parsedRef };
            }
            return nameCellPairs;
        }

        #endregion

        #region Events
        //--------------------------------------------------------------------
        // Events
        //--------------------------------------------------------------------

        #endregion


        //--------------------------------------------------------------------
        // Nested Classes
        //--------------------------------------------------------------------

        /// <summary>
        /// Contains the mapping information about a cell - column relation.
        /// </summary>
        private class CellToColumnMapping
        {
            #region Properties
            //--------------------------------------------------------------------
            // Properties
            //--------------------------------------------------------------------

            internal int DataColumnIndex { get; set; }
            internal IXLCell ExcelCell { get; set; }
            internal CellReference ExcelCellRef { get; set; }

            #endregion
        }

    }
}